四.MySQL 表数据结构 | KWAN的解忧杂货铺

# 四.MySQL 表数据结构

# 1.mysql 的索引数据结构?

数据结构特点:

底层使用的数据结构是 b+树
查询时间复杂度是 O(logN)
叶子节点存储数据
非叶子节点不存储数据

B+树是为磁盘或其他直接存取设备设计的一种平衡查找树。在 B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点上，通过各叶子节点指针进行连接。

B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在 2 ～ 4 层，这也就是说在查找某一键值的行记录时最多只需要 2 到 4 次 IO，这倒不错。因为当前一般的机械磁盘每秒至少可以做 100 次 IO， 2 ～ 4 次的 IO 意味着查询时间只需 0.02 ～ 0.04 秒。

# 2.聚集索引和辅助索引

数据库中的 B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index)，但是不管是聚集索引还是辅助索引，其内部都是 B+树的结构，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引，是按表中的主键顺序存放数据的，叶子节点也称为数据页，每个数据页通过双向链表和其他页进行关联。

对于辅助索引(SecondaryIndex，也称非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

# 3.高度为 4 的 B+树能存储的数据?

假设每条 SQL 是 1kb，主键 id 是 bigint 类型，一棵高度为 4 的 b+树能存储多少数据。

在 innodb 存储引擎里面，最小的存储单元是页(page)，一个页的大小是 16KB。

查看页的大小语句:这就说明了一个页的大小为 16384B，也就是 16kb。

show variables like 'innodb_page_size';

#innodb_page_size	 16384

1
2
3

一个页的大小为 16kb 假设一行数据的大小是 1kb，那么一个页可以存放 16 行这样的数据.那如果想查找某个页里面的一个数据的话，得首先找到他所在的页.innodb 存储引擎使用 B+树的结构来存储数据。如果是在主键上建立的索引就是聚簇索引，即只有在叶子节点才存储行数据，而非叶子节点里面的内容其实是键值和指向数据页的指针。

因此，我们首先解决一个简单一点的问题：如果是 2 层的 B+树，最多可以存储多少行数据？如果是 2 层的 B+树，即存在一个根节点和若干个叶子节点，那么这棵 B+树的存放总记录数为：根节点指针数单个叶子节点记录行数。因为单个页的大小为 16kb，而一行数据的大小为 1kb，也就是说一页可以存放 16 行数据。然后因为非叶子节点的结构是：“页指针+键值”，我们假设主键 ID 为 bigint 类型，长度为 8 字节（byte），而指针大小在 InnoDB 源码中设置为 6 字节（byte），这样一共 14 字节（byte），因为一个页可以存放 16k 个 byte，所以一个页可以存放的指针个数为 16384/14=1170 个。因此一个两层的 B+树可以存放的数据行的个数为：

一页可以存放1170个指针，一页可以存放16行的数据，1170x16=18720(行)

那么对于高度为 3 的 B+树呢？也就是说第一层的页，即根页可以存放 1170 个指针，然后第二层的每个页也可以存放 1170 个指针。这样一共可以存放 1170x1170 个指针，所以一共可以存放 1170x1170x16=21902400（2 千万左右）行记录。也就是说一个三层的 B+树就可以存放千万级别的数据了。

而每经过一个节点都需要 IO 一次，把这个页数据从磁盘读取到缓存，也就是说读取一个数据只需要三次 IO。继续来说，高度为 4 的 B+树呢？1170x1170x1170x16 约等于 200 亿。

# 4.为什么选用 B+树做索引?

众多的数据结构在逻辑层面可分为：线性结构和非线性结构。

线性结构有：数组、链表，基于它们衍生出的有哈希表（哈希表也称散列表）、栈、队列等。
非线性结构有：树、图。
还有其他数据结构如：跳表、位图也都由基础数据结构演化而来，不同的数据结构存在即都是为了解决某些场景问题。

索引虽然存储在磁盘上，但使用索引查找数据时，可以从磁盘先读取索引放到内存中，再通过索引从磁盘找到数据；再然后将磁盘读取到的数据也放到内存里。索引就让磁盘和内存强强联手.

b 树（balance tree）和 b+树应用在数据库索引，可以认为是 m 叉的多路平衡查找树，但是从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？因为我们要考虑磁盘 IO 的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少 IO 次数，对于树来说，IO 次数就是树的高度，而“矮胖”就是 b 树的特征之一，它的每个节点最多包含 m 个孩子，m 称为 b 树的阶。为什么不用 B 树呢？ B+树，是 B 树的一种变体，查询性能更好。 B+树相比于 B 树的查询优势：

B+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”。B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；
B+树查询必须查找到叶子节点，B 树只要匹配到即可直接返回。因此 B+树查找更稳定（并不慢），必须查找到叶子节点；而 B 树，如果数据在根节点，最快，在叶子节点最慢，查询效率不稳定。
对于范围查找来说，B+树只需遍历叶子节点链表即可，并且不需要排序操作，因为叶子节点已经对索引进行了排序操作。B 树却需要重复地中序遍历，找到所有的范围内的节点。

总结选用 B+树的原因:

要尽少在磁盘做 I/O 操作。
要能尽快的按照区间高效地范围查找。
- 等值查询：哈希、跳表，不适合范围查询。
- 范围查询：树结构，但是会带来磁盘 IO 的情况，也会造成树过高的问题，所以衍生出 B 树，
- 而 B 树又因为范围查询的情况下，会一直到根节点再到叶子节点这样查询。所以延伸出了 B+树，解决范围查询的情况。
- 查询效率，B 树的话不稳定，O(1-logN)之间，而 B+树的话可以稳定在 O(logN)

# 5.为什么不用 hash 表做索引?

为什么用 B+树做索引，而不用 hash 表做索引

模糊查找不支持：哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而 B+树则可以通过最左前缀原则快速找到对应的数据。
范围查找不支持：如果我们要进行范围查找，例如查找 ID 为 100~400 的人，哈希表同样不支持，只能遍历全表。
哈希冲突问题：索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

# 6.什么是索引组织表?

在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table)在 InnoDB 存储引擎表中，每张表都有个主键(Primary Key)，如果在创建表时没有显式地定义主键，则 InnoDB 存储引擎会按如下方式选择或创建主键:

首先判断表中是否有非空的唯一索引(Unique NOT NULL)，如果有，则该列即为主键。
如果不符合上述条件，InnoDB 存储引擎自动创建一个6 字节大小的指针。

当表中有多个非空唯一索引时，InnoDB 存储引警将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

# 7.innodb 逻辑存储结构

从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block)，InnoDB 存储引擎的逻辑存储结构大致如图所示。

# 8.共享表空间

在默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1，即所有数据都存放在这个表空间内。如果用户启用了参数 innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。

如果启用了 innodb_file_per_table 的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap 页，其他类的数据，如回滚(undo)信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。这同时也说明了另一个问题:即使在启用了参数 innodb_fle_per_table 之后，共享表空间还是会不断地增加其大小。

# 9.innodb 中的段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB 存储引擎表是索引组织的(index organized)，因此数据即索引，索引即数据。

数据段即为 B+树的叶子节点(Leaf node segment)
索引段即为 B+树的非索引节点(Non-leaf node segment)

# 10.innodb 中的区

区是由连续页组成的空间，在任何情况下每个区的大小都为 1MB。为了保证区中页的连续性，InnoDB 存储引擎一次从磁盘申请 4~5 个区。在默认情况下，InnoDB 存储引擎页的大小为 16KB，即一个区中一共有 64 个连续的页。

InnoDB1.0.x 版本开始引入压缩页，即每个页的大小可以通过参数 KEY_BLOCK_SIZE 设置为 2K、4K、8K，因此每个区对应页的数量就应该为 512、256、128。这是因为区的大小固定为 1M

InnoDB1.2x 版本新增了参数 innodb_page_size，通过该参数可以将默认页的大小设置为 4K、8K，但是页中的数据库不是压缩。这时区中页的数量同样也为 256、128。总之，不论页的大小怎么变化，区的大小总是为 1M。

# 11.innodb 中的页

同大多数数据库一样，InnoDB 有页(Page)的概念(也可以称为块)，页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中。默认每个页的大小为 16KB。而从 InnoDB1.2x 版本开始，可以通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K。若设置完成，则所有表中页的大小都为 innodb_page_size，不可以对其再次进行修改。除非通过 mysql dump 导入和导出操作来产生新的库。

在 InnoDB 存储引擎中，常见的页类型有:

数据页(B-tree Node)
undo 页(undo Log Page)
系统页(System Page)
事务数据页(Transaction system Page)
插入缓冲位图页(Insert Buffer Bitmap)
插入缓冲空闲列表页(Insert Buffer Free List)
未压缩的二进制大对象页(Uncompressed BLOB Page)
压缩的二进制大对象页(compressed BLOB Page)

# 12.innodb 中的行

InnoDB 存储引擎是面向行的(row-oriented)，也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放 16KB/2~200 行的记录，即 7992 行记录。这里提到了 row-oriented 的数据库，也就是说，存在有 column-oriented 的数据库。比如 ClockHouse，这对于数据仓库下的分析类 SQL 语句的执行及数据压缩非常有帮助。

行格式

Compact: 在 MySQL5.1 版本中，默认设置为 Compact 紧凑行格式
Redundant: Redundant 格式是为兼容之前版本而保留的
Dynamic: 动态行格式

用户可以通过命令查看当前表使用的行格式。其中 row_format 属性表示当前所使用的行记录结构类型。如:

SHOW TABLE STATUS LIKE 'mysql_user';

# 13.Compact 行格式

Compact 行记录是在 MySQL5.0 中引入的，其设计目标是高效地存储数据。简单来说，一个页中存放的行数据越多，其性能就越高。

Compact 行格式结构

变长字段长度列表	NULL 标志位	记录头信息	列 1 数据	列 2 数据	列数据 xxxx

Compact 行记录格式的首部是一个非 NULL 变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为:

若列的长度小于 255 字节，用 1 字节表示;
若大于 255 个字节，用 2 字节表示。

变长字段的长度最大不可以超过 2 字节，这是因在 MySQL 数据库中，VARCHAR 类型的最大长度限制为 65535。变长字段之后的第二个部分是 NULL 标志位，该位指示了该行数据中是否有 NULL 值，有则用 1 表示。该部分所占的字节应该为 1 字节。接下来的部分是记录头信息(record header)，固定占用 5 字节(40 位)，每位的含义见表 4-1。

最后的部分就是实际存储每个列的数据。需要要特别注意的是，NULL 不占该部分任何空间，即 NULL 除了占有 NULL 标志位，实际存储不占有任何空间。另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务 ID 列和回滚指针列，分别为 6 字节和 7 字节的大小。若 InnoDB 表没有定义主键，每行还会增加一个 6 字节的 rowid 列。

SQL 中的NULL值认为是列中最小的值，放在 b+树的最左边.

IS NULL、IS NOT NULL、!=不一定不用索引，需要看优化器的优化情况.

# 14.Redundant 行格式

Redundant 行格式结构

Redundant 行格式是为了兼容以前版本的页格式，Redundant 是冗余的意思

字段长度偏移列表	记录头信息	列 1 数据	列 2 数据	列数据 xxxx

不同于 Compact 行记录格式，Redundant 行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。若列的长度小于 255 字节，用 1 字节表示;若大于 255 字节，用 2 字节表示。第二个部分为记录头信息(record header)，不同于 Compact 行记录格式，Redundant 行记录格式的记录头占用 6 字节(48 位)，每位的含义见表 4-2。从表 4-2 中可以发现， n_felds 值代表一行中列的数量，占用 10 位。同时这也很好地解释了为什么 MySQL数据库一行支持最多的列为 1023。2 的 10 次方为 1024.

对于 VARCHAR 类型的 NULL 值，Redundant 行记录格式同样不占用任何存储空间，而 CHAR 类型的 NULL 值需要占用空间。

# 15.Compressed 和 Dynamic

InnoDB1.0.x 版本开始引入了新的文件格式(file format，用户可以理解为新的页格式)，以前支持的 Compact 和 Redundant 格式称为 Antelope 文件格式，新的文件格式称为 Barracuda 文件格式。 Barracuda 文件格式下拥有两种新的行记录格式:Compressed 和 Dynamic

新的两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式，如图下图所示，在数据页中只存放 20 个字节的指针，实际的数据都存放在 Off Page 中，而之前的 Compact 和 Redundant 两种格式会存放 768 个前缀字节。

Compressed 行记录格式的另一个功能就是，存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

Compressed和Dynamic行格式:

Compact 和 Redundant行格式:

# 16.行溢出数据

在 MySQL 中一行除了 blob 及 text 类的大字段之外，其余字段的长度之和不能超过 65535，最大支持 65535 字节，实际执行会报错，实际支持 65532 字节.使用 latin1 字符集时是 65532 字节，使用 utf8 编码时，utf8 编码占 3 位，最大支持 21844 字节.

mysql 理论支持的Row Size Limit 是 65535 为什么实际是 65532

这是因为 MySQL 需要额外的字节来存储一些内部数据，例如行格式信息和 NULL 位图。

具体来说，每个行都需要几个字节来存储行格式信息和 NULL 位图。行格式信息用于指示行的格式，例如是否使用动态行格式或静态行格式。NULL 位图用于表示行中哪些列具有 NULL 值。这些额外的字节会占用行的空间，从而减少了实际可用的行大小。NULL 位图占一个字节

任何存储引擎都不能超过65535

mysql> CREATE TABLE t (a VARCHAR(10000)， b VARCHAR(10000)，
       c VARCHAR(10000)， d VARCHAR(10000)， e VARCHAR(10000)，
       f VARCHAR(10000)， g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type， not counting BLOBs， is 65535. This includes storage overhead，
check the manual. You have to change some columns to TEXT or BLOBs

1
2
3
4
5
6

mysql> CREATE TABLE t (a VARCHAR(10000)， b VARCHAR(10000)，
       c VARCHAR(10000)， d VARCHAR(10000)， e VARCHAR(10000)，
       f VARCHAR(10000)， g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type， not counting BLOBs， is 65535. This includes storage overhead，
check the manual. You have to change some columns to TEXT or BLOBs

1
2
3
4
5
6

非空可以创建成功:

CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;

1
2
3

可以为空的场景:

CREATE TABLE t2222
       (c1 VARCHAR(65532) DEFAULT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;

1
2
3

65535 是指所有 varchar 列的总和，不是单个 varchar 的字节数量

一个页为 16kb，为 16384 字节，如何存储 65535 字节的呢?

但是当发生行溢出时，数据存放在项类型为 Uncompress BLOB 页中。

# 执行报错
CREATE TABLE tmp_max_length_one_field
(
  a VARCHAR(65535)
) CHARSET = latin1
  ENGINE = InnoDB;

# 可以执行
CREATE TABLE tmp_max_length_one_field
(
  a VARCHAR(65532)
) CHARSET = latin1
  ENGINE = InnoDB;
 # 报错
  CREATE TABLE tmp_max_length_mut_field
(
  a VARCHAR(22222)，
  b VARCHAR(22222)，
  c VARCHAR(22222)，
  d VARCHAR(22222)
) CHARSET = latin1
  ENGINE = InnoDB;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 17.字段个数

Column Count Limits (opens new window)

Column Count Limits 表字段最大限制.官方文档的内容如下，主要意思是字段个数限制达不到理想的 4096 个，且和字段类型有关。

MySQL 中 innodb 引擎的字段上限是 1017。
MySQL 中 MyISAM 引擎表最多可以存 2598 个字段。

受影响的因素:

存储引擎
表的单行最大行影响
单个列的存储要求

# 18.行最大字节

Row Size Limits (opens new window)

Row Size Limits 行的最大字节数.MySQL 表的内部表示具有 65535 字节的最大行大小限制，即使存储引擎能够支持更大的行。BLOB 和 TEXT 列只占行大小限制的 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。

对于 4KB、8KB、16KB 和 32KB InnoDB_page_size 设置，应用于本地存储在数据库页面中的数据的 InnoDB 表的最大行大小略小于页面的一半。例如，对于默认的 16KB InnoDB 页面大小，最大行大小略小于 8KB。对于 64KB 的页面，最大行大小略小于 16KB。

如果包含可变长度列的行超过了 InnoDB 的最大行大小，InnoDB 会选择可变长度列用于外部页外存储，直到该行符合 InnoDB 的行大小限制。对于页外存储的可变长度列，本地存储的数据量因行格式而异。

不同的存储格式使用不同数量的页眉和尾部数据，这会影响行的可用存储量。

# 19.数据页对行大小限定

在 InnoDB 中，每个数据页（database page）的大小可以通过设置 innodb_page_size 参数进行配置，常见的大小包括 4KB、8KB、16KB 和 32KB，还有更大的 64KB 页。

在 InnoDB 中，行数据存储在数据库页内部。然而，为了保证数据库的性能和效率，InnoDB 对于每个页内的数据行大小有一定的限制。对于 4KB、8KB、16KB 和 32KB 的页面大小设置，InnoDB 将数据行大小限制在略小于半个数据库页的大小。这意味着，对于 4KB 页，数据行大小将略小于 2KB，对于 8KB 页，数据行大小将略小于 4KB，以此类推。

对于 64KB 的页面大小设置，InnoDB 将数据行大小限制在略小于 16KB。换句话说，对于 64KB 页，数据行大小将略小于 16KB。

这些限制是为了确保在数据库页内存储的数据行不会过大，从而避免内存浪费和性能下降。同时，这些限制还有助于在处理和查询数据时提高效率，因为较小的数据行大小可以提高数据访问速度和内存利用率。

这些限制是为了优化 InnoDB 存储引擎的性能和效率，同时避免过大的数据行带来的问题。

# 20.innodb 的页结构

页的总体结构图:

# 21.数据页的结构

InnoDB为了不同的目的而设计了许多种不同类型的页。常见的页类型有数据页(索引页)、Undo 页、系统页、事务数据页等。InnoDB 数据页由以下 7 个部分组成

File Header(文件头)
Page Header(页头)
Infimun 和 Supremum Records
User Records(用户记录，即行记录)
Free Space(空闲空间)
Page Directory(页目录)
File Trailer(文件结尾信息)

字段名	中文名	大小	简单描述
File Header	文件头部	38 字节	页的一些通用信息
Page Header	页面头部	56 字节	数据页专有的一些信息
Infimum + Supremum	最小记录和最大记录	26 字节	两个虚拟的行记录
User Records	用户记录	不确定	实际存储的行记录内容（大小不确定）
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页面目录	不确定	页中的某些记录的相对位置
File Trailer	文件尾部	8 字节	校验页是否完整

# 22.File Header

File Header 用来记录页的一些头信息，由表表 4-3 中 8 个部分组成，共占用 38 字节。

FILE_PAGE_TYPE 页类型:

接着 File Header 部分的是 Page Header，该音部分用来记录数据页的状态信息，由 14 个部分组成，共占用 56 字节，如表 4-5 所示。

字段名	大小	描述
PAGE_N_DIR_SLOTS	2 字节	页目录中的槽数量
PAGE_HEAP_TOP	2 字节	未使用的空间最小地址，即 Free Space 之后的地址
PAGE_N_HEAP	2 字节	本页中的记录数量（包括最小、最大记录和删除的记录）
PAGE_FREE	2 字节	第一个已标记为删除的记录地址
PAGE_GARBAGE	2 字节	已删除记录占用的字节数
PAGE_LAST_INSERT	2 字节	最后插入记录的位置
PAGE_DIRECTION	2 字节	记录插入的方向
PAGE_N_DIRECTION	2 字节	一个方向连续插入的记录数量
PAGE_N_RECS	2 字节	该页中记录的数量（不包括最小、最大记录和删除的记录）
PAGE_MAX_TRX_ID	8 字节	修改当前页的最大事务 ID
PAGE_LEVEL	2 字节	当前页在 B+树中所处的层级
PAGE_INDEX_ID	8 字节	索引 ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF	10 字节	B+树叶子段的头部信息（仅在 B+树的 Root 页定义）
PAGE_BTR_SEG_TOP	10 字节	B+树非叶子段的头部信息（仅在 B+树的 Root 页定义）

Infimun 和 Supremum Records

在 InnoDB 存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum 记录是比该页中任何主键值都要小的值， Supremum 指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。

# 24.User Records

存储行格式的地方，行格式的记录头信息

字段名	大小	描述
预留位 1	1 位	没有使用
预留位 2	1 位	没有使用
delete_mask	1 位	标记该记录是否被删除
min_rec_mask	1 位	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4 位	表示当前记录拥有的记录数
heap_no	13 位	表示当前记录在记录堆的位置信息
record_type	3 位	表示当前记录的类型 0 表示普通记录 1 表示 B+树非叶子节点记录 2 表示最小记录 3 表示最大记录
next_record	16 位	表示下一条记录的相对位置

delete_mask:删除标记为 1 记录不会立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

next_record(单链表):表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的 next_record 值为 32，意味着从第一条记录的真实数据的地址处向后找 32 个字节便是下一条记录的真实数据。

注意：

下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum 记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录

未被删除的记录(delete_mask = 0)会按照主键从小到大的顺序形成了一个单链表

# 25.Free Space

自己存储的记录会按照我们指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

# 26.Page Directory(页目录)

页目录的过程:

将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录（此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。

注意

对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

分组过程

初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加 1，表示本组内又添加了一条记录，直到该组中的记录数等于 8 个。
在一个组中的记录数等于 8 个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中 4 条记录，另一个 5 条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

查询记录的过程:

过程分为两步：

通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用所谓的二分法来进行快速查找。4 个槽的编号分别是：0、1、2、3、4，所以初始情况下最低的槽就是low=0，最高的槽就是high=4。比方说我们想找主键值为6的记录，过程是这样的：

计算中间槽的位置：(0+4)/2=2，所以查看槽2对应记录的主键值为8，又因为8 > 6，所以设置high=2，low保持不变。
重新计算中间槽的位置：(0+2)/2=1，所以查看槽1对应的主键值为4，又因为4 < 6，所以设置low=1，high保持不变。
因为high - low的值为 1，所以确定主键值为5的记录在槽2对应的组中。此刻我们需要找到槽2中主键值最小的那条记录，然后沿着单向链表遍历槽2中的记录。但是我们前面又说过，每个槽对应的记录都是该组中主键值最大的记录，这里槽2对应的记录是主键值为8的记录，怎么定位一个组中最小的记录呢？别忘了各个槽都是挨着的，我们可以很轻易的拿到槽1对应的记录（主键值为4），该条记录的下一条记录就是槽2中主键值最小的记录，该记录的主键值为5。所以我们可以从这条主键值为5的记录出发，遍历槽2中的各条记录，直到找到主键值为6的那条记录即可。由于一个组中包含的记录条数只能是 1~8 条，所以遍历一个组中的记录的代价是很小的。

# 27.File Trailer

为了检测页是否已经完整地写入磁盘(如可能发生的写入过程中磁盘损坏、机器关机等)，InnoDB 存储引擎的页中设置了File Trailer 部分。

File Trailer 只有一个 FIL_PAGE_END_LSN 部分，占用 8 字节。前 4 字节代表该页的 checksum 值，最后 4 字节和 File Header 中的 FIL_PAGE_LSN 相同。将这两个值与 File Header 中的 FIL_PAGE_SPACE_OR_CHKSUM 和 FIL_PAGE_LSN 值进行比较看是否一致(checksum 的比较需要通过 InnoDB 的 checksum 函数来进行比较，不是简单的等值比较)，以此来保证页的完整性(not corrupted)。

在默认配置下，InnoDB 存储引擎每次从磁盘读取一个页就会检测该页的完整性，即页是否发生损坏，这就是通过 File Trailer 部分进行检测，而该部分的检测会有一定的开销。用户可以通过参数 innodb_log_checksums 来开启或关闭对这个页完整性的检查。默认是开启的。

# 28.页分裂

页分裂是指在 B-tree 索引结构中的页满时，需要分裂成两个页来容纳新的数据。

随机插入可能导致页分裂，因为它会在索引中引起不必要的调整，增加了性能开销。相反，如果插入方向是连续的，MySQL 可能只需要在当前页的末尾追加新记录，而无需进行页分裂。这可以提高插入性能，因为它减少了数据结构的调整次数。

另一方面，批量插入通常能够提高性能，因为它减少了插入操作的开销。当大量数据需要插入时，一次性插入多条记录比逐条插入更有效率。这与页分裂的问题有些相关，但批量插入的性能优势通常是独立于插入方向的。

# 29.数据存储

# 1. VARCHAR 类型定义

VARCHAR类型可以存储长度可变的字符串，括号内的数字表示最大存储长度。例如，VARCHAR(50)表示该字段可以存储最多 50 个字符的字符串，而VARCHAR(500)则可以存储最多 500 个字符。

# 2. 存储空间差异

虽然VARCHAR类型在定义时指定了最大长度，但实际上它只存储实际输入的字符数量。这意味着无论定义为VARCHAR(50)还是VARCHAR(500)，如果实际存储的字符串长度相同，它们所占用的存储空间是相同的。然而，数据库在内部存储时会包括额外的字节来记录实际长度，这可能会导致即使是存储相同长度的字符串，VARCHAR(500)也会占用更多的存储空间。

# 3. 性能考虑

索引效率：VARCHAR类型的索引效率会受到定义长度的影响。较短的VARCHAR字段（如VARCHAR(50)）在索引时通常会更快，因为索引需要存储的数据量较小。
内存使用：数据库在处理查询时，会将数据加载到内存中。较长的VARCHAR字段可能会占用更多的内存，影响查询性能。

# 4.问题背景

varchar(50)和 varchar(500)有什么区别？内存空间都一样的话，我为什么不都用 varchar(500)呢？

在数据库设计中，选择合适的数据类型对于性能和存储效率至关重要。VARCHAR是一种常用的字符串类型，允许存储可变长度的字符串。VARCHAR(50)和VARCHAR(500)是两种不同的VARCHAR类型定义，它们在长度上有所区别，但更深层次的影响则涉及到数据库性能、存储效率以及索引优化等多个方面。

# 5. 为什么不应该统一使用`VARCHAR(500)`

存储效率：虽然VARCHAR(500)可以存储更长的字符串，但在大多数情况下，实际存储的字符串长度远小于 500。这将导致存储空间的浪费。
查询性能：使用较长的VARCHAR字段可能会增加查询处理的复杂性，尤其是在进行全文搜索或使用 LIKE 语句进行模式匹配时。
索引优化：较短的VARCHAR字段在创建索引时更为高效，因为索引需要处理的数据量更小。

# 6. 最佳实践

定义合适的长度：根据实际需求定义VARCHAR字段的长度，避免过度定义。
避免过度索引：对于非常长的VARCHAR字段，考虑是否需要全文索引，或者是否可以使用其他类型的索引来优化查询。
使用合适的数据类型：对于非常短的字符串，考虑使用CHAR类型，因为它在存储空间和性能上可能更有优势。

← 02-MySQL 文件 04-主从同步 →